﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace DAL
{
    public class DbHelper
    {
        //connection
        private SqlConnection connection;
        public SqlConnection Connection
        {

            get
            {
                if (connection == null)
                {
                    string conStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
                    connection = new SqlConnection(conStr);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                return connection;
            }
        }
        //command
        public int ExecuteCommand(string sql)
        {
            using (SqlCommand cmd = new SqlCommand(sql, Connection))
            {
                int result = cmd.ExecuteNonQuery();
                connection.Close();
                return result;
            }
        }
        public int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            using (SqlCommand cmd = new SqlCommand(sql, Connection))
            {
                cmd.Parameters.AddRange(values);
                int result = cmd.ExecuteNonQuery();
                return result;
            }
        }
        /// <summary>
        /// 获取 DataTable
        /// </summary>
        public DataTable GetDataTable(string sql)
        {
            using (DataTable dt = new DataTable())
            {
                SqlCommand cmd = new SqlCommand(sql, Connection);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                da.Dispose();
                connection.Close();
                return dt;
            }
        }
        /// <summary>
        /// 获取 DataTable
        /// </summary>
        public DataTable GetDataTable(string sql, params SqlParameter[] values)
        {
            using (DataTable dt = new DataTable())
            {
                SqlCommand cmd = new SqlCommand(sql, Connection);
                cmd.Parameters.AddRange(values);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                da.Dispose();
                connection.Close();
                return dt;
            }
        }

        //获取数量
        public int GetScalar(string sql)
        {
            using (SqlCommand cmd = new SqlCommand(sql, Connection))
            {
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                connection.Close();
                return result;
            }
        }
        public int GetScalar(string sql, params SqlParameter[] values)
        {
            using (SqlCommand cmd = new SqlCommand(sql, Connection))
            {
                cmd.Parameters.AddRange(values);
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                connection.Close();
                return result;
            }
        }
        public object GetScalarobj(string sql)
        {
            using (SqlCommand cmd = new SqlCommand(sql, Connection))
            {
                object obj = cmd.ExecuteScalar();
                connection.Close();
                return obj;
            }
        }


    }
}
